{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:100% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# encoding: utf-8\n",
    "import pymongo, json\n",
    "import pandas as pd\n",
    "from pandas import Series, DataFrame\n",
    "from scipy.stats import pearsonr\n",
    "import numpy as np\n",
    "\n",
    "pd.set_option('display.width', None)  # 设置字符显示宽度\n",
    "pd.set_option('display.max_rows', None)  # 设置显示最大行\n",
    "pd.set_option('display.max_columns', None)  # 设置显示最大行\n",
    "\n",
    "client = pymongo.MongoClient('localhost', 27017)\n",
    "futures = client.futures3\n",
    "position = futures.position\n",
    "market = futures.market\n",
    "unit = futures.unit\n",
    "\n",
    "start = '20190101'\n",
    "end = '20200206'\n",
    "var = 'JD'\n",
    "\n",
    "position = DataFrame(list(position.find({'date': {'$gte': start}},'var':var)))\n",
    "market = DataFrame(list(market.find({'date': {'$gte': start}},'var':var)))\n",
    "unit = DataFrame(list(unit.find()))\n",
    "# position = position[position['long_party_name'].notna()]\n",
    "\n",
    "var1 = position[position['variety'] == var]\n",
    "\n",
    "long_party_name = var1['long_party_name']\n",
    "short_party_name = var1['short_party_name']\n",
    "party_name = long_party_name.append(short_party_name)\n",
    "\n",
    "long = var1.groupby(['date', 'variety', 'long_party_name'])[['long_open_interest', 'long_open_interest_chg']].sum()\n",
    "# print(long)\n",
    "short = var1.groupby(['date', 'variety', 'short_party_name'])[['short_open_interest', 'short_open_interest_chg']].sum()\n",
    "# 合并\n",
    "frames = [long, short]\n",
    "position1 = pd.concat(frames, axis=1, sort=True).fillna(0).reset_index()\n",
    "# print(position.head())\n",
    "# 净持仓\n",
    "position1['当日净持仓'] = position1.apply(lambda x: x['long_open_interest'] - x['short_open_interest'], axis=1)\n",
    "# 字段更名\n",
    "position2 = position1.rename(columns={'level_0': 'date', 'level_1': 'variety', 'level_2': 'mem'})\n",
    "var2 = position2[position2['variety'] == var]\n",
    "\n",
    "df = pd.DataFrame()\n",
    "\n",
    "for i in party_name:\n",
    "    try:\n",
    "        mem = var2[var2['mem'] == i]\n",
    "        position_behind = mem.shift(-1)\n",
    "        # print(position_behind)\n",
    "\n",
    "# 合并滞后和原始数据\n",
    "        all_position = pd.merge(var2, position_behind, right_index=True, left_index=True)\n",
    "        all_position = all_position[['date_y', 'variety_y', 'mem_y', 'long_open_interest_y','long_open_interest_chg_y', 'short_open_interest_y', 'short_open_interest_chg_y','当日净持仓_y', '当日净持仓_x']].dropna()\n",
    "#\n",
    "#         # 更名\n",
    "        all_position= all_position.rename(columns={'date_y': 'date', 'variety_y': 'variety', 'mem_y': '会员',\n",
    "                                                    'long_open_interest_y': '多单量', 'long_open_interest_chg_y':'多单增减量','short_open_interest_y': '空单量','short_open_interest_chg_y':'空单增减量',\n",
    "                                                    '当日净持仓_y': '当日净持仓', '当日净持仓_x': '昨日净持仓'})\n",
    "        # print(all_position)\n",
    "        all_position['净持仓变化量'] = all_position.apply(lambda x: x['当日净持仓'] - x['昨日净持仓'], axis=1)\n",
    "\n",
    "        # print(all_position.head())\n",
    "        market['change'] = market.apply(lambda x: x['close'] - x['open'], axis=1)\n",
    "\n",
    "        var3 = market[market['variety'] == var]\n",
    "\n",
    "        chg = var3[['date', 'variety', 'change']]\n",
    "        chg = chg.groupby(['date', 'variety'])['change'].sum()\n",
    "\n",
    "        hb = pd.merge(chg, all_position, on=['date', 'variety'], how='outer').dropna().drop_duplicates()\n",
    "\n",
    "        chgs = hb['change']\n",
    "        nets = hb['净持仓变化量']\n",
    "        p = chgs.corr(nets)\n",
    "\n",
    "        hb = hb[hb['date'] <= end]\n",
    "#\n",
    "        d = [{'var': var,'start': start,'end':end,'会员': i,'相关性': p,'样本':hb['净持仓变化量'].count(),'多单量':hb['多单量'].values[-1],'多单增减量':hb['多单增减量'].values[-1],'空单量':hb['空单量'].values[-1],'空单增减量':hb['空单增减量'].values[-1],'当日净持仓': hb['当日净持仓'].values[-1], '昨日净持仓': hb['昨日净持仓'].values[-1], '净持仓变化量': hb['净持仓变化量'].values[-1]}]\n",
    "        df1=pd.DataFrame(d)\n",
    "        df=df.append(df1)\n",
    "\n",
    "    except:\n",
    "        continue\n",
    "        \n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df = df.sort_values('相关性', ascending=False)\n",
    "mean=df.groupby(['var','start','end'])['样本'].mean()[0]\n",
    "df=df[df['样本'] >= mean]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "df=df[df['相关性']>0]\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "corr=df.groupby(['var','start','end'])['相关性'].mean()[0]\n",
    "corr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "df=df[df['相关性'] >= corr]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "part_sum=df.groupby(['var','start','end'])['会员'].sum()\n",
    "part_sum=part_sum.reset_index()\n",
    "netss=df.groupby(['var','start','end'])[['多单量','多单增减量','空单量','空单增减量','当日净持仓','昨日净持仓','净持仓变化量']].sum()\n",
    "netss['corr']=df['相关性'].mean()\n",
    "data=pd.merge(part_sum,netss,on=['end'])\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 市值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#合约相乘\n",
    "\n",
    "market['cv']=market.apply(lambda x:x['close'] * x['open_interest'],axis=1)\n",
    "market=market.groupby(['date','variety'])[['cv','open_interest']].sum()\n",
    "market['close_c']=market['cv']/market['open_interest']\n",
    "market=market.reset_index()\n",
    "market=pd.merge(market,unit[['variety','unit']],on=['variety'],how='outer')\n",
    "market"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "market['市值']=market.apply(lambda x:x['close_c'] * x['unit'],axis=1)\n",
    "market"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "position=pd.merge(position,market[['date','variety','市值']],on=['date','variety'],how='outer')\n",
    "position"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "position['价值']=position.apply(lambda x:x['净持仓']*x['市值'],axis=1)\n",
    "position"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
